﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using XRERP.Models;
using XRERP.Models.laborcost;
using XRERP.Models.otherfeesqk;
using XRERP.Models.probill;

namespace XRERP.Controllers
{
    /// <summary>
    /// 结算单
    /// </summary>
    public class StatementsController : Controller
    {
        private MySqlDB db = new MySqlDB();

        // GET: Statements
        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 查询请款单名细
        /// </summary>
        /// <param name="client_name"></param>
        /// <param name="pro_name"></param>
        /// <param name="engineer_name"></param>
        /// <param name="pro_time_start"></param>
        /// <param name="pro_time_end"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public ActionResult getInvoices(
            String client_name="",
            String pro_name="",
            String engineer_name="",
            String pro_time_start = "",
            String pro_time_end = "",
            int page = 1, int rows = 10
            )
        {

            var data = from d in db.xr_bl_proarc
                       from a in db.xr_bl_probillmain
                       from b in db.xr_bl_clientinfo
                       from c in db.xr_bl_proinfo
                       where
                       a.proarc_uuid == d.proarc_uuid
                       && d.clientinfo_uuid == b.clientinfo_uuid
                       && d.proinfo_uuid == c.proinfo_uuid
                       && a.probill_flag=="1"
                       && a.validflag == "1"
                       select new
                       {
                           a.probill_number,
                           a.probillmain_uuid,
                           a.proarc_number,
                           a.proarc_uuid,
                           a.pro_address,
                           a.engineer_name,
                           a.settlement_total,
                           pro_time=a.pro_time.ToString(),
                           b.client_name,
                           c.pro_name
                       };
            if (!client_name.Equals(""))
            {
                data = data.
                    Where(x => x.client_name.Contains(client_name));
            }

            if (!pro_name.Equals(""))
            {
                data = data.
                    Where(x => x.pro_name.Contains(pro_name));
            }

            if (!engineer_name.Equals(""))
            {

                data = data.
                    Where(x => x.engineer_name.Contains(engineer_name));
            }

            if (!pro_time_start.Equals(""))
            {
                data = data.
                    Where(x => x.pro_time != "" && x.pro_time.CompareTo(pro_time_start) <= 0);

            }
            if (!pro_time_end.Equals(""))
            {
                data = data.
                    Where(x => x.pro_time != "" && x.pro_time.CompareTo(pro_time_end) <= 0);

            }

            var table = new
            {
                rows = data
                .OrderBy(y => y.probill_number)
                .Skip(rows * (page - 1))
                .Take(rows)
                .ToList(),
                total = data.Count()
            };


            return Json(table,JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 生成结算单前置数据
        /// </summary>
        /// <param name="probillmain_uuid">uuid</param>
        /// <returns></returns>
        public ActionResult getStatements(String probillmain_uuid)
        {
            
            //try
            //{
                //人工计算
                String laborcostsql = @"select DATE_FORMAT(t.ground_time,'%Y%m') as release_month,
                                    SUM(t.real_rent) as real_rent from xr_bl_laborcost t
                                    where t.validflag='1' and t.probillmain_uuid=?
                                    group by DATE_FORMAT(t.ground_time, '%Y%m')
                                    ORDER BY DATE_FORMAT(t.ground_time,'%Y%m')";
                var laborcost = db.Database.SqlQuery<Tmp_Group_laborcost>(laborcostsql, probillmain_uuid);


                String OtherFeesqksql = @"select DATE_FORMAT(t.gen_time,'%Y%m') as gen_time,
                                    t.amount_money
                                    from xr_bl_othercost t
                                    where t.validflag='1' and t.probillmain_uuid=?
                                    GROUP BY DATE_FORMAT(t.gen_time,'%Y%m')
                                    ORDER BY DATE_FORMAT(t.gen_time,'%Y%m')";
                var OtherFeesqk = db.Database.SqlQuery<Tmp_Group_otherfeesqk>(OtherFeesqksql, probillmain_uuid);

                String probillsql = @"select t2.lease_date,t2.leasedate_total from xr_bl_probill t1,
                            xr_bl_probilldetail t2
                            where t1.probill_uuid=t2.probill_uuid
                            and t1.probillmain_uuid=?
                            and t1.validflag='1'
                            ORDER BY t2.lease_date
                            ";
                var probill = db.Database.SqlQuery<Tmp_Group_probill>(probillsql, probillmain_uuid);

                //结算单
                List<xr_bl_settlementsheet> settlementsheet = new List<xr_bl_settlementsheet>();



                //获取人工费用列表
                Tmp_Group_laborcost lf;
                Tmp_Group_laborcost ll;

                if (null != laborcost)
                {
                    lf = laborcost.First();
                    ll = laborcost.Last();
                }
                else
                {
                    lf = new Tmp_Group_laborcost { real_rent = 0, release_month = "0" };
                    ll = new Tmp_Group_laborcost { real_rent = 0, release_month = "0" };
                }

                Tmp_Group_otherfeesqk of;
                Tmp_Group_otherfeesqk ol;

                if (null != OtherFeesqk)
                {
                    of = OtherFeesqk.First();
                    ol = OtherFeesqk.Last();
                }
                else
                {
                    of = new Tmp_Group_otherfeesqk { amount_money = 0, gen_time = "0" };
                    ol = new Tmp_Group_otherfeesqk { amount_money = 0, gen_time = "0" };
                }

                Tmp_Group_probill pf;
                Tmp_Group_probill pl;

                if (null != probill)
                {
                    pf = probill.First();
                    pl = probill.Last();
                }
                else
                {
                    pf = new Tmp_Group_probill { leasedate_total = 0, lease_date = "0" };
                    pl = new Tmp_Group_probill { leasedate_total = 0, lease_date = "0" };
                }

                //if(!lf.release_month.Equals("0")&&!of.gen_time.Equals("0"))
                string mindate = MIN(MIN(lf.release_month, of.gen_time), pf.lease_date);
                string maxdate = MAX(MAX(ll.release_month, ol.gen_time), pl.lease_date);

                
                int year = int.Parse(maxdate.Substring(0, 4)) - int.Parse(mindate.Substring(0, 4));

                int month = year * 12 + int.Parse(maxdate.Substring(4, 2)) - int.Parse(mindate.Substring(4, 2)) + 1;

                DateTime root = DateTime.Parse(mindate.Substring(0,4)+"-"+ mindate.Substring(4, 2) + "-01");

                for (int i=1;i<=month;i++)
                {
                    xr_bl_settlementsheet seetlement = null;

                    //换算出当前结算月份
                    string settlement_month = root.ToString("yyyyMM");

                    //实签租金
                    if (null != laborcost)
                    {
                        var l = laborcost.ToList();
                        for(int a=0;a<l.Count;a++)
                        {
                            if (l[a].release_month.Equals(settlement_month))
                            {
                                seetlement = new xr_bl_settlementsheet();
                                seetlement.settlement_month = settlement_month;
                                seetlement.real_rent = l[a].real_rent;
                            }
                        }
                    }
                    //其它费用
                    if (null != OtherFeesqk)
                    {
                        var l = OtherFeesqk.ToList();
                        for (int a = 0; a < l.Count; a++)
                        {
                            if (l[a].gen_time.Equals(settlement_month))
                            {
                                if (null == seetlement)
                                {
                                    seetlement = new xr_bl_settlementsheet();
                                    seetlement.settlement_month = settlement_month;
                                }
                                seetlement.othersercharg = l[a].amount_money;
                            }
                        }
                    }

                    //请款单
                    if (null != probill)
                    {
                        var l = probill.ToList();
                        for (int a = 0; a < l.Count; a++)
                        {
                            if (l[a].lease_date.Equals(settlement_month))
                            {
                                if (null == seetlement)
                                {
                                    seetlement = new xr_bl_settlementsheet();
                                    seetlement.settlement_month = settlement_month;
                                }
                                seetlement.rec_rent = l[a].leasedate_total;
                                seetlement.month_rent= l[a].leasedate_total;
                        }
                        }
                    }

                    if(null!= seetlement)
                    {
                        settlementsheet.Add(seetlement);
                    }

                    root=root.AddMonths(1);

                }


                /*return Json(new {
                    rows= settlementsheet,
                    total= settlementsheet.Count()
                });*/
                return Json(settlementsheet);
            /*}
            catch(Exception e)
            {
                return Json(e.ToString());
            }*/

           
        }

        /// <summary>
        /// 取最小值
        /// </summary>
        /// <param name="time1"></param>
        /// <param name="time2"></param>
        /// <returns></returns>
        private string MIN(string time1,string time2)
        {
            if (time1.Equals("0"))
                return time2;
            if (time2.Equals("0"))
                return time1;

            if (int.Parse(time1) < int.Parse(time2))
                return time1;
            else return time2;
        }

        /// <summary>
        /// 取最大值
        /// </summary>
        /// <param name="time1"></param>
        /// <param name="time2"></param>
        /// <returns></returns>
        private string MAX(string time1, string time2)
        {
            if (time1.Equals("0"))
                return time2;
            if (time2.Equals("0"))
                return time1;

            if (int.Parse(time1) < int.Parse(time2))
                return time2;
            else return time1;
        }

    }
}